package com.sh.data.engine.domain.util;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQuery;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.util.FnvHash;
import com.alibaba.fastjson.JSONArray;
import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.sh.data.engine.common.exception.BusinessException;
import gudusoft.gsqlparser.*;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.nodes.TTableList;
import gudusoft.gsqlparser.stmt.*;
import org.apache.commons.lang3.StringUtils;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author: zigui.zdf @Date: 2022/3/2 16:30:15 @Description:
 */
public class SqlParserUtil {

    private static Map<String, String> REPLACE_STR = Maps.newHashMap();

    private static Map<String, String> RE_REPLACE_STR = Maps.newHashMap();

    static {
        REPLACE_STR.put("\u2018", "$hufu1");
        REPLACE_STR.put("\u2019", "$hufu2");
        REPLACE_STR.put("\uFF08", "$hufu3");
        REPLACE_STR.put("\uFF09", "$hufu4");
        REPLACE_STR.put("\uFF0C", "$hufu5");

        RE_REPLACE_STR.put("$hufu1", "\u2018"); // ‘
        RE_REPLACE_STR.put("$hufu2", "\u2019"); // ’
        RE_REPLACE_STR.put("$hufu3", "\uFF08"); // （
        RE_REPLACE_STR.put("$hufu4", "\uFF09"); // ）
        RE_REPLACE_STR.put("$hufu5", "\uFF0C"); // ，
    }

    public static void main(String[] args) {
//    String sql3213 = "ALTER TABLE ods_test1 ADD column name2 varchar(255) ;";
//    List<String> split = split(sql3213, DbType.postgresql);
//    System.out.println(split.toString());
        String sql213 = "select\r1";
        List<String> split = SqlParserUtil.split(sql213, DbType.postgresql);
        split.add(sql213);
        for (String s : split) {
            System.out.println(s);
        }
        String jsonString = JSONArray.toJSONString(split);
        System.out.println(jsonString);

        String sql32 = "select date_part('day',delivery_date - to_timestamp(REGEXP_REPLACE(supper_deliver_date, E'[\\\\r\\\\n\\\\s\\u4e00- \\u9fa5]+', '','g'), 'YYY-MM-DD HH24:MI:SS')) day_gap from order_dimension_analysis;select 2";
        List<String> split3 = sapValidSplitSql(sql32, EDbVendor.dbvpostgresql);
//    System.out.println(split);
        String sqlSAP = "CREATE TABLE \"CFDEVAGENT\".\"ods_WJCJCSB\" ( \"ID\" varchar(500) ,\"NAME\" varchar(500) ,\"COMMENT\" varchar(500) ) UNLOAD PRIORITY 5 AUTO MERGE;\n";
        List<String> split1 = split(sqlSAP, DbType.sapdb);
        System.out.println();
        String sql =
            "insert into b select * from a ;\n"
                + "select * from b;\n"
                + "-- 这是注释\n"
                + " select * from c";
        String sql2 =
            "CREATE COLUMN TABLE \"CFDATA\".\"DWS_QM_VEHINFO_FEED_TOTAL_R\" (\"FAREAID\" NVARCHAR(10) COMMENT '市场',\n"
                + "\"FFACTORY\" NVARCHAR(50) COMMENT '工厂',\n"
                + "\"FCATEGORY\" NVARCHAR(50) COMMENT '车辆类型（两轮/四轮）',\n"
                + "\"FSERIES\" NVARCHAR(50) COMMENT '车系列',\n"
                + "\"FCARTYPE\" NVARCHAR(50) COMMENT '车型',\n"
                + "\"FSUPPLIER\" NVARCHAR(255) COMMENT '供应商',\n"
                + "\"FSUPPLIERNO\" VARCHAR(50) COMMENT '供应商编号',\n"
                + "\"FMATERIAL\" NVARCHAR(255) COMMENT '投料编号',\n"
                + "\"FMATERIALNAME\" VARCHAR(255) COMMENT '投料名称',\n"
                + "\"FBASEDATE\" VARCHAR(10) COMMENT '基准日期',\n"
                + "\"FDTMONTH\" VARCHAR(10) COMMENT '倒推月份',\n"
                + "\"FFEED_QTY\" DECIMAL(18,\n"
                + "4) CS_FIXED COMMENT '投料数量',\n"
                + "\"INSERT_TIME\" LONGDATE CS_LONGDATE COMMENT '固化时间') UNLOAD PRIORITY 5 AUTO MERGE COMMENT 'QIS:车型/物料/供应商投料汇总(倒推月份)-ppm'";

        // Map<String, String> consanguinityForSap = getConsanguinityForSap(sql22);
        //    consanguinityForSap.forEach(
        //        (e, a) -> {
        //          System.out.println(e);
        //          System.out.println(a);
        //        });
        //    final SQLStatementParser sqlStatementParser =
        //        SQLParserUtils.createSQLStatementParser(sql, DbType.mysql, false);
        //
        //    final List<SQLStatement> sqlStatements = sqlStatementParser.parseStatementList();
        //
        //    for (SQLStatement sqlStatement : sqlStatements) {
        //      System.out.println(sqlStatement.toString());
        //    }
        //    Boolean aBoolean = checkSql(sql2, DbType.mysql);
        //    List<String> split = split(sql2, DbType.sapdb);
        //    System.out.println(split);
//    checkIsSelectSQl(sql);
    }

    /**
     * 判断是否是查询语句
     */
    public static boolean checkIsSelectSQl(String sql, EDbVendor eDbVendor) {
        TGSqlParser sqlParser = new TGSqlParser(eDbVendor);
        sqlParser.sqltext = sql;
        int parse = sqlParser.parse();
        if (parse == 0) {
            TCustomSqlStatement tCustomSqlStatement = sqlParser.sqlstatements.get(0);
            // tCustomSqlStatement.
            if (tCustomSqlStatement instanceof TSelectSqlStatement) {
                return true;
            }
        }
        return false;
    }

    /**
     * sap 血缘解析
     *
     * @param sql
     * @return
     */
    public static String getCreateTableAndView(String sql) {
        TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvhana);
        sqlParser.sqltext = sql;
        TSourceTokenList sourcetokenlist = sqlParser.getSourcetokenlist();
        System.out.println(sourcetokenlist.toString());
        StringBuilder sb = new StringBuilder();
        int parse = sqlParser.parse();
        if (parse == 0) {
            TCustomSqlStatement tCustomSqlStatement = sqlParser.sqlstatements.get(0);
            if (tCustomSqlStatement instanceof TCreateTableSqlStatement) {
                TCreateTableSqlStatement tCreateTableSqlStatement =
                    (TCreateTableSqlStatement) sqlParser.sqlstatements.get(0);
                String s = tCreateTableSqlStatement.getTargetTable().toString();
                if (StringUtils.isNotBlank(s)) {
                    sb.append(s);
                }
            }
            if (tCustomSqlStatement instanceof TCreateViewSqlStatement) {
                TCreateViewSqlStatement tCreateViewSqlStatement =
                    (TCreateViewSqlStatement) sqlParser.sqlstatements.get(0);
                String s = tCreateViewSqlStatement.getViewName().toString();
                if (StringUtils.isNotBlank(s)) {
                    sb.append(s);
                }
            }
        }
        return sb.toString();
    }

    public static boolean checkUpdateMdSQL(String sql) {
        TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvhana);
        sqlParser.sqltext = sql;
        TSourceTokenList sourcetokenlist = sqlParser.getSourcetokenlist();
        System.out.println(sourcetokenlist.toString());
        int parse = sqlParser.parse();
        if (parse == 0) {
            TCustomSqlStatement tCustomSqlStatement = sqlParser.sqlstatements.get(0);
            if (tCustomSqlStatement instanceof TUpdateSqlStatement) {
                TUpdateSqlStatement tCreateTableSqlStatement =
                    (TUpdateSqlStatement) sqlParser.sqlstatements.get(0);
                String s = tCreateTableSqlStatement.getTargetTable().toString();
                if (StringUtils.isNotBlank(s)) {
                    if (s.startsWith("md_")) {
                        return true;
                    }
                }
            }
            if (tCustomSqlStatement instanceof TAlterTableStatement) {
                TAlterTableStatement tAlterTableStatement =
                    (TAlterTableStatement) sqlParser.sqlstatements.get(0);
                String s = tAlterTableStatement.getTargetTable().toString();
                if (StringUtils.isNotBlank(s)) {
                    if (s.startsWith("md_")) {
                        return true;
                    }
                }
            }
        }
        return false;
    }

    public static Map<String, String> getConsanguinityForSap(String sql) {
        TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvhana);
        HashMap<String, String> map = Maps.newHashMap();
        sqlParser.sqltext = sql;
        TSourceTokenList sourcetokenlist = sqlParser.getSourcetokenlist();
        System.out.println(sourcetokenlist.toString());
        int parse = sqlParser.parse();
        if (parse == 0) {
            TCustomSqlStatement tCustomSqlStatement = sqlParser.sqlstatements.get(0);
            // tCustomSqlStatement.
            if (tCustomSqlStatement instanceof TInsertSqlStatement) {
                TInsertSqlStatement insert = (TInsertSqlStatement) sqlParser.sqlstatements.get(0);
                // 目标表
                String targetTable = insert.getTargetTable().toString();
                // 获取子查询
                TSelectSqlStatement subQuery = insert.getSubQuery();
                List<String> sourceTableList = Lists.newArrayList();
                // 获取来源表
                getSourceTable(sourceTableList, subQuery);
                // 去重、去null
                List<String> sources =
                    sourceTableList.stream()
                        .distinct()
                        .filter(Objects::nonNull)
                        .collect(Collectors.toList());
                map.put("source", Joiner.on(",").join(sources));
                map.put("target", targetTable);
            }
        }
        return map;
    }

    public static void getSourceTable(List sourceTableList, TSelectSqlStatement subQuery) {
        // FIXME
        // ArrayList<String> tableList = Lists.newArrayList();
        if (subQuery != null) {
            TTableList tables = subQuery.getTables();
            if (tables.size() != 0) {
                for (TTable table : tables) {
                    String source = table.getTableName().toString();
                    if (source.equals("subquery")) {
                        TSelectSqlStatement subquery = table.getSubquery();
                        TTableList tables1 = subquery.getTables();
                        TSelectSqlStatement leftStmt = subquery.getLeftStmt();
                        TSelectSqlStatement rightStmt = subquery.getRightStmt();
                        if (tables1 != null) {
                            for (TTable tableinner : tables1) {
                                TSelectSqlStatement subquery1 = tableinner.getSubquery();
                                getSourceTable(sourceTableList, subquery1);
                            }
                        }
                        if (leftStmt != null) {
                            TTableList tables2 = leftStmt.getTables();
                            tables2.forEach(
                                table3 -> {
                                    sourceTableList.add(table3.toString());
                                });
                        }
                        if (rightStmt != null) {
                            TTableList tables2 = leftStmt.getTables();
                            tables2.forEach(
                                table3 -> {
                                    sourceTableList.add(table3.toString());
                                });
                        }
                        sourceTableList.add(tables1.toString());
                    } else {
                        sourceTableList.add(source);
                    }
                    //
                }
            }
            // return tableList.stream().distinct().filter(Objects::nonNull).collect(Collectors.toList());
            return;
        }
        // return tableList.stream().distinct().filter(Objects::nonNull).collect(Collectors.toList());
        return;
    }

    /**
     * 分割sql
     *
     * @param sqlText
     * @param dbType
     * @return
     * @throws ParserException
     */
    public static List<String> split(String sqlText, DbType dbType) throws ParserException {
        sqlText = replaceStr(sqlText);
        if (dbType.equals(DbType.kingbase)) {
            dbType = DbType.postgresql;
        }

        if (StringUtils.isBlank(sqlText)) {
            return Collections.emptyList();
        }

        if (dbType.equals(DbType.postgresql)) {
            List<String> strings = SqlParserUtil.sapValidSplitSql(sqlText, EDbVendor.dbvpostgresql);
            strings = reReplaceStr(strings);
            return strings;
        }

        if (dbType.equals(DbType.sapdb)) {
            List<String> strings = SqlParserUtil.sapValidSplitSql(sqlText, EDbVendor.dbvhana);
            strings = reReplaceStr(strings);
            return strings;
        }

        if (dbType.equals(DbType.mysql)) {
            List<String> strings = SqlParserUtil.sapValidSplitSql(sqlText, EDbVendor.dbvmysql);
            strings = reReplaceStr(strings);
            return strings;
        }

        List<String> sqls = Lists.newArrayList();

        final SQLStatementParser sqlStatementParser =
            SQLParserUtils.createSQLStatementParser(sqlText, dbType, false);

        final List<SQLStatement> sqlStatements = sqlStatementParser.parseStatementList();

        for (SQLStatement sqlStatement : sqlStatements) {
            sqls.add(sqlStatement.toString());
        }

        return sqls;
    }

    private static String replaceStr(String sqlText) {
        List<String> str = REPLACE_STR.keySet().stream().collect(Collectors.toList());

        for (String s : str) {
            sqlText = sqlText.replace(s, REPLACE_STR.get(s));
        }
        return sqlText;
    }

    private static List<String> reReplaceStr(List<String> sqls) {
        List<String> reStr = RE_REPLACE_STR.keySet().stream().collect(Collectors.toList());

        List<String> res = Lists.newArrayList();
        for (String sql : sqls) {
            for (String s : reStr) {
                sql = sql.replace(s, RE_REPLACE_STR.get(s));
            }
            res.add(sql);
        }
        return res;
    }

    public static List<String> sapValidSplitSql(String script, EDbVendor dbVendor) {
        String regex = "[\u00A0\u2018\u2019\u200B\uFF08\uFF09\uFF0C]";
        script = script.replaceAll(regex, "$hufu");

        List<String> res = Lists.newArrayList();

        TGSqlParser sqlParser = new TGSqlParser(dbVendor);
        sqlParser.sqltext = script;
        int parse = sqlParser.parse();
        TStatementList stList = sqlParser.sqlstatements;

        for (int i = 0; i < stList.size(); i++) {
            TCustomSqlStatement customSt = stList.get(i);
            String sapSql = customSt.toString();
            res.add(sapSql);
        }
        return res;
    }

    /**
     * sql校验
     *
     * @param sql
     * @param dbType
     * @return
     */
    public static Boolean checkSql(String sql, DbType dbType) {
        final SQLStatementParser sqlStatementParser = new SQLStatementParser(sql, dbType);
        Boolean flag = true;
        try {
            final List<SQLStatement> sqlStatements = sqlStatementParser.parseStatementList();
            SQLSelectStatement sqlStatement = (SQLSelectStatement) sqlStatements.get(0);
            SQLSelectQuery query = sqlStatement.getSelect().getQuery();
            if (query instanceof SQLSelectQueryBlock) {
                SQLSelectQueryBlock q = (SQLSelectQueryBlock) query;
                List<SQLSelectItem> selectList = q.getSelectList();
                selectList.forEach(
                    i -> {
                        System.out.println("line82->" + i.toString());
                        System.out.println(i.getExpr());
                        System.out.println(i.getAlias());
                        SQLExpr expr = i.getExpr();
                        long l = FnvHash.fnv1a_64_lower(expr.toString());
                        long concat = FnvHash.fnv1a_64_lower("CONCAT");
                        System.out.println(l);
                        System.out.println(concat);
                    });
            }
        } catch (Exception e) {
            flag = false;
            throw new BusinessException(e.getMessage());
        }

        return flag;
    }
}
